package com.example.express.dao.impl;

import com.example.express.bean.Express;
import com.example.express.dao.BaseExpressDao;
import com.example.express.exception.DuplicateCodeException;
import com.example.express.util.DruidUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExpressDaoImpl implements BaseExpressDao {

    /**
     * 用于查询快递总数和日新增，待取件总数和日新增
     */
    private static final String SQL_CONSOLE = "SELECT " +
            "COUNT(ID) data1_size," +
            "COUNT(TO_DAYS(in_time)=TO_DAYS(NOW()) OR NULL) data1_day," +
            "COUNT(STATUS=0 OR NULL) data2_size," +
            "COUNT(TO_DAYS(in_time)=TO_DAYS(NOW()) AND STATUS=0 OR NULL) data2_day " +
            "FROM t_express";

    /**
     * 查询全部快递
     */
    private static final String SQL_FIND_ALL="SELECT * FROM t_express";

    /**
     * 分页查询全部快递信息
     */
    private static final String SQL_FIND_ALL_LIMIT = "SELECT * FROM t_express LIMIT ?,?";

    /**
     * 依据快递编号查询快递信息
     */
    private static final String SQL_FIND_BY_NUMBER = "SELECT * FROM t_express WHERE number = ?";

    /**
     * 依据取件码查询快递信息
     */
    private static final String SQL_FIND_BY_CODE = "SELECT * FROM t_express WHERE code = ?";

    /**
     * 依据用户手机号查询快递信息
     */
    private static final String SQL_FIND_BY_USEPHONE = "SELECT * FROM t_express WHERE userphone = ?";

    private static final String SQL_FIND_BY_USEPHONE_AND_STATUS = "SELECT * FROM t_express WHERE userphone = ? AND status = ?";

    /**
     * 依据录入人电话查询快递信息
     */
    private static final String SQL_FIND_BY_SYSPHONE = "SELECT * FROM t_express WHERE sysphone = ?";

    /**
     * 录入快递信息
     */
    private static final String SQL_INSERT = "INSERT INTO t_express(number,username,userphone,company,code,in_time,status,sysphone) values (?,?,?,?,?,NOW(),0,?)";

    /**
     * 修改快递信息
     */
    private static final String SQL_UPDATE = "UPDATE t_express SET number=?,username=?,company=?,status=? WHERE id = ?";

    /**
     * 修改快递状态
     */
    private static final String SQL_UPDATE_STATUS = "UPDATE t_express SET status=1,out_time=NOW(),code=null WHERE code = ?";

    /**
     * 删除快递信息
     */
    private static final String SQL_DELETE = "DELETE FROM t_express WHERE id = ?";


    /**
     * 用于查询全部快递（总数+新增），代取件（总数+新增）
     *
     * @return[{size:总数,day:新增},{size:总数,day:新增}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement stat = null;
        ResultSet rs = null;
        List<Map<String,Integer>> list = new ArrayList<>();
        Map<String,Integer> map1 = new HashMap<>();
        Map<String,Integer> map2 = new HashMap<>();
        try {
            stat = conn.prepareStatement(SQL_CONSOLE);
            rs = stat.executeQuery();
            while(rs.next()){
                int data1_size = rs.getInt("data1_size");
                int data1_day = rs.getInt("data1_day");
                int data2_size = rs.getInt("data2_size");
                int data2_day = rs.getInt("data2_day");
                map1.put("data1_size",data1_size);
                map1.put("data1_day",data1_day);
                map2.put("data2_size",data2_size);
                map2.put("data2_day",data2_day);
                list.add(map1);
                list.add(map2);
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,stat,rs);
        }
        return list;
    }

    /**
     * 分页查询全部快递信息
     *
     * @param limit
     * @param offset
     * @param pageNum
     * @return[Express{快递信息}]
     */
    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNum) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement stat = null;
        ResultSet rs = null;
        List<Express> list = new ArrayList<>();
        try {
            if (limit) {
                stat = conn.prepareStatement(SQL_FIND_ALL_LIMIT);
                stat.setInt(1,offset);
                stat.setInt(2,pageNum);
            }
            else {
                stat = conn.prepareStatement(SQL_FIND_ALL);
            }
            rs = stat.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String number = rs.getString("number");
                String username = rs.getString("username");
                String userphone = rs.getString("userphone");
                String company = rs.getString("company");
                String code = rs.getString("code");
                Timestamp inTime = rs.getTimestamp("in_time");
                Timestamp outTime = rs.getTimestamp("out_time");
                int status = rs.getInt("status");
                String sysPhone = rs.getString("sysphone");
                Express e = new Express(id,number,username,userphone,company,code,inTime,outTime,status,sysPhone);
                list.add(e);
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,stat,rs);
        }
        return list;
    }

    /**
     * 根据单号查询快递信息
     *
     * @param number
     * @return {快递信息}
     */
    @Override
    public Express findByNumber(String number) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        Express e = null;
        try {
            state = conn.prepareStatement(SQL_FIND_BY_NUMBER);
            state.setString(1,number);
            rs = state.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String userphone = rs.getString("userphone");
                String company = rs.getString("company");
                String code = rs.getString("code");
                Timestamp inTime = rs.getTimestamp("in_time");
                Timestamp outTime = rs.getTimestamp("out_time");
                int status = rs.getInt("status");
                String sysPhone = rs.getString("sysphone");
                e = new Express(id,number,username,userphone,company,code,inTime,outTime,status,sysPhone);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtils.close(conn,state,rs);
        }
        return e;
    }

    /**
     * 根据取件码查询快递信息
     *
     * @param code
     * @return {快递信息}
     */
    @Override
    public Express findByCode(String code) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        Express e = null;
        try {
            state = conn.prepareStatement(SQL_FIND_BY_CODE);
            state.setString(1,code);
            rs = state.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String number = rs.getString("number");
                String username = rs.getString("username");
                String userphone = rs.getString("userphone");
                String company = rs.getString("company");
                Timestamp inTime = rs.getTimestamp("in_time");
                Timestamp outTime = rs.getTimestamp("out_time");
                int status = rs.getInt("status");
                String sysPhone = rs.getString("sysphone");
                e = new Express(id,number,username,userphone,company,code,inTime,outTime,status,sysPhone);
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,rs);
        }
        return e;
    }

    /**
     * 根据用户手机号查询快递信息
     *
     * @param userPhone
     * @return [Express{快递信息}]
     */
    @Override
    public List<Express> findByUserPhone(String userPhone) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        List<Express> data = new ArrayList<>();
        try {
            state = conn.prepareStatement(SQL_FIND_BY_USEPHONE);
            state.setString(1,userPhone);
            rs = state.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String number = rs.getString("number");
                String username = rs.getString("username");
                String company = rs.getString("company");
                String code = rs.getString("code");
                Timestamp inTime = rs.getTimestamp("in_time");
                Timestamp outTime = rs.getTimestamp("out_time");
                int status = rs.getInt("status");
                String sysPhone = rs.getString("sysphone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,rs);
        }
        return data;
    }

    @Override
    public List<Express> findByUserPhoneAndStatus(String userPhone, int status) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        List<Express> data = new ArrayList<>();
        try {
            state = conn.prepareStatement(SQL_FIND_BY_USEPHONE_AND_STATUS);
            state.setString(1,userPhone);
            state.setInt(2,status);
            rs = state.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String number = rs.getString("number");
                String username = rs.getString("username");
                String company = rs.getString("company");
                String code = rs.getString("code");
                Timestamp inTime = rs.getTimestamp("in_time");
                Timestamp outTime = rs.getTimestamp("out_time");
                String sysPhone = rs.getString("sysphone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,rs);
        }
        return data;
    }

    /**
     * 根据录入人手机号查询快递信息
     *
     * @param sysPhone
     * @return [Express{快递信息}]
     */
    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        List<Express> data = new ArrayList<>();

        try {
            state = conn.prepareStatement(SQL_FIND_BY_SYSPHONE);
            state.setString(1,sysPhone);
            rs = state.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String number = rs.getString("number");
                String username = rs.getString("username");
                String userphone = rs.getString("userphone");
                String company = rs.getString("company");
                String code = rs.getString("code");
                Timestamp inTime = rs.getTimestamp("in_time");
                Timestamp outTime = rs.getTimestamp("out_time");
                int status = rs.getInt("status");
                Express e = new Express(id,number,username,userphone,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,rs);
        }
        return data;
    }

    /**
     * 录入快递信息
     *
     * @param e
     * @return 返回true则表示录入成功，false录入失败
     * @throws DuplicateCodeException
     */
    @Override
    public boolean insert(Express e) throws DuplicateCodeException {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            //number,username,userphone,company,code,in_time,status,sysphone
            state.setString(1, e.getNumber());
            state.setString(2,e.getUsername());
            state.setString(3,e.getUserPhone());
            state.setString(4, e.getCompany());
            state.setString(5,e.getCode());
            state.setString(6, e.getSysPhone());
            return state.executeUpdate()>0 ?true:false;
        } catch (SQLException e1) {
            if(e1.getMessage().endsWith("for key 'code'")){
                //是因为取件码重复,而出现了异常
                DuplicateCodeException e2 = new DuplicateCodeException(e1.getMessage());
                throw e2;
            }else{
                e1.printStackTrace();
            }
        }finally {
            DruidUtils.close(conn,state,null);
        }
        return false;
    }

    /**
     * 根据id更新快递信息
     *
     * @param id
     * @param newExpress
     * @return
     */
    @Override
    public boolean update(int id, Express newExpress) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            //number=?,username=?,company=?,status=? WHERE id = ?
            state.setString(1, newExpress.getNumber());
            state.setString(2,newExpress.getUsername());
            state.setString(3,newExpress.getCompany());
            state.setInt(4,newExpress.getStatus());
            state.setInt(5,id);
            return state.executeUpdate()>0 ? true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtils.close(conn,state,null);
        }
        return false;
    }

    /**
     * 更新快递状态为1表示取件完成
     *
     * @param code
     * @return 返回true表示更新成功，false则表示更新失败
     */
    @Override
    public boolean updateStatus(String code) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE_STATUS);
            state.setString(1,code);
            return state.executeUpdate()>0?true:false;
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtils.close(conn,state,null);
        }
        return false;
    }

    /**
     * 根据id删除快递信息
     *
     * @param id
     * @return 返回true表示删除成功，false表示失败
     */
    @Override
    public boolean delete(int id) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_DELETE);
            state.setInt(1,id);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,null);
        }
        return false;
    }
}
